﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using ServiceStack.Data;
using ServiceStack.OrmLite;

namespace ServiceStack.Auth;

public class OrmLiteAuthRepository : OrmLiteAuthRepository<UserAuth, UserAuthDetails>, IUserAuthRepository
{
    public OrmLiteAuthRepository(IDbConnectionFactory dbFactory) : base(dbFactory) { }

    public OrmLiteAuthRepository(IDbConnectionFactory dbFactory, string? namedConnection = null) 
        : base(dbFactory, namedConnection) {}
}

public partial class OrmLiteAuthRepository<TUserAuth, TUserAuthDetails>(
    IDbConnectionFactory dbFactory,
    string? namedConnection = null)
    : OrmLiteAuthRepositoryBase<TUserAuth, TUserAuthDetails>
    where TUserAuth : class, IUserAuth
    where TUserAuthDetails : class, IUserAuthDetails
{
    public string? NamedConnection { get; private set; } = namedConnection;
    static void ConfigureDb(IDbConnection db) => db.WithTag(nameof(OrmLiteAuthRepository));

    protected IDbConnection OpenDbConnection()
    {
        return this.NamedConnection != null
            ? dbFactory.Open(NamedConnection,ConfigureDb)
            : dbFactory.Open(ConfigureDb);
    }

    public override void Exec(Action<IDbConnection> fn)
    {
        using var db = OpenDbConnection();
        fn(db);
    }

    public override T Exec<T>(Func<IDbConnection, T> fn)
    {
        using var db = OpenDbConnection();
        return fn(db);
    }
}

public class OrmLiteAuthRepositoryMultitenancy : OrmLiteAuthRepositoryMultitenancy<UserAuth, UserAuthDetails>, IUserAuthRepository
{
    public OrmLiteAuthRepositoryMultitenancy(IDbConnection db) : base(db) { }

    public OrmLiteAuthRepositoryMultitenancy(IDbConnectionFactory dbFactory, params string[] connectionStrings)
        : base(dbFactory, connectionStrings) { }
}

public partial class OrmLiteAuthRepositoryMultitenancy<TUserAuth, TUserAuthDetails> : OrmLiteAuthRepositoryBase<TUserAuth, TUserAuthDetails>, IDisposable
    where TUserAuth : class, IUserAuth
    where TUserAuthDetails : class, IUserAuthDetails
{
    private readonly IDbConnection db;
    static void ConfigureDb(IDbConnection db) => db.WithTag(nameof(OrmLiteAuthRepositoryMultitenancy));

    public OrmLiteAuthRepositoryMultitenancy(IDbConnection db)
    {
        this.db = db;
    }

    private readonly IDbConnectionFactory dbFactory;
    private readonly string[]? connectionStrings;

    public OrmLiteAuthRepositoryMultitenancy(IDbConnectionFactory dbFactory, params string[] connectionStrings)
    {
        this.dbFactory = dbFactory;
        this.connectionStrings = connectionStrings;
    }

    public override void Exec(Action<IDbConnection> fn)
    {
        if (db == null)
            throw new NotSupportedException("This operation can only be called within context of a Request");

        fn(db);
    }

    public override T Exec<T>(Func<IDbConnection, T> fn)
    {
        if (db == null)
            throw new NotSupportedException("This operation can only be called within context of a Request");

        return fn(db);
    }

    public void EachDb(Action<IDbConnection> fn)
    {
        if (dbFactory == null)
            throw new NotSupportedException("This operation can only be called on Startup");

        var ormLiteDbFactory = (OrmLiteConnectionFactory)dbFactory;

        foreach (var connStr in connectionStrings.Safe())
        {
            //Required by In Memory Sqlite
            var db = connStr == ormLiteDbFactory.ConnectionString
                ? dbFactory.Open(ConfigureDb)
                : dbFactory.OpenDbConnectionString(connStr,ConfigureDb);

            using (db)
            {
                fn(db);
            }
        }
    }

    public override void InitSchema()
    {
        hasInitSchema = true;

        EachDb(InitSchema);
    }

    public override void DropSchema()
    {
        EachDb(DropSchema);
        hasInitSchema = false;
    }

    public override void DropAndReCreateTables()
    {
        hasInitSchema = true;
        EachDb(DropAndReCreateTables);
    }

    public override void InitApiKeySchema()
    {
        EachDb(db =>
        {
            db.CreateTableIfNotExists<ApiKey>();
        });
    }

    public override void DropApiKeySchema()
    {
        EachDb(db =>
        {
            db.DropTable<ApiKey>();
        });
    }

    public void Dispose()
    {
        db?.Dispose();
    }
}

public abstract partial class OrmLiteAuthRepositoryBase<TUserAuth, TUserAuthDetails> 
    : IUserAuthRepository, IRequiresSchema, IClearable, IManageRoles, IManageApiKeys, ICustomUserAuth, IQueryUserAuth
    where TUserAuth : class, IUserAuth
    where TUserAuthDetails : class, IUserAuthDetails
{
    public bool hasInitSchema;

    public bool UseDistinctRoleTables { get; set; }

    public bool ForceCaseInsensitiveUserNameSearch { get; set; } = true;

    public abstract void Exec(Action<IDbConnection> fn);

    public abstract T Exec<T>(Func<IDbConnection, T> fn);

    public virtual void InitSchema()
    {
        hasInitSchema = true;
        Exec(InitSchema);
    }
        
    public virtual void InitSchema(IDbConnection db)
    {
        db.CreateTableIfNotExists<TUserAuth>();
        db.CreateTableIfNotExists<TUserAuthDetails>();
        if (UseDistinctRoleTables)
            db.CreateTableIfNotExists<UserAuthRole>();
        hasInitSchema = true;
    }

    public virtual void DropSchema()
    {
        Exec(DropSchema);
        hasInitSchema = false;
    }
    public virtual void DropSchema(IDbConnection db)
    {
        if (UseDistinctRoleTables)
            db.DropTable<UserAuthRole>();
        db.DropTable<TUserAuthDetails>();
        db.DropTable<TUserAuth>();
        hasInitSchema = false;
    }

    public virtual void DropAndReCreateTables()
    {
        hasInitSchema = true;
        Exec(DropAndReCreateTables);
    }
    public virtual void DropAndReCreateTables(IDbConnection db)
    {
        db.DropAndCreateTable<TUserAuth>();
        db.DropAndCreateTable<TUserAuthDetails>();
        if (UseDistinctRoleTables)
            db.DropAndCreateTable<UserAuthRole>();
    }

    public virtual bool InitCheck()
    {
        return hasInitSchema = Exec(db => 
            db.TableExists<TUserAuth>()
            && db.TableExists<TUserAuthDetails>()
            && (!UseDistinctRoleTables || db.TableExists<UserAuthRole>()));
    }

    public virtual IUserAuth CreateUserAuth(IUserAuth newUser, string password) => Exec(db => CreateUserAuth(db, newUser, password));
    public virtual IUserAuth CreateUserAuth(IDbConnection db, IUserAuth newUser, string password)
    {
        newUser.ValidateNewUser(password);
        AssertNoExistingUser(db, newUser);

        newUser.PopulatePasswordHashes(password);
        newUser.CreatedDate = DateTime.UtcNow;
        newUser.ModifiedDate = newUser.CreatedDate;

        db.Save((TUserAuth)newUser);

        newUser = db.SingleById<TUserAuth>(newUser.Id);
        return newUser;
    }

    protected virtual void AssertNoExistingUser(IDbConnection db, IUserAuth newUser, IUserAuth exceptForExistingUser = null)
    {
        if (newUser.UserName != null)
        {
            var existingUser = GetUserAuthByUserName(db, newUser.UserName);
            if (existingUser != null
                && (exceptForExistingUser == null || existingUser.Id != exceptForExistingUser.Id))
                throw new ArgumentException(ErrorMessages.UserAlreadyExistsFmt.LocalizeFmt(newUser.UserName.SafeInput()));
        }
        if (newUser.Email != null)
        {
            var existingUser = GetUserAuthByUserName(db, newUser.Email);
            if (existingUser != null
                && (exceptForExistingUser == null || existingUser.Id != exceptForExistingUser.Id))
                throw new ArgumentException(ErrorMessages.EmailAlreadyExistsFmt.LocalizeFmt(newUser.Email.SafeInput()));
        }
    }

    public virtual IUserAuth UpdateUserAuth(IUserAuth existingUser, IUserAuth newUser, string password) =>
        Exec(db => UpdateUserAuth(db, existingUser, newUser, password));

    public virtual IUserAuth UpdateUserAuth(IDbConnection db, IUserAuth existingUser, IUserAuth newUser, string password)
    {
        newUser.ValidateNewUser(password);

        AssertNoExistingUser(db, newUser, existingUser);

        newUser.Id = existingUser.Id;
        newUser.PopulatePasswordHashes(password, existingUser);
        newUser.CreatedDate = existingUser.CreatedDate;
        newUser.ModifiedDate = DateTime.UtcNow;

        db.Save((TUserAuth)newUser);

        return newUser;
    }

    public virtual IUserAuth UpdateUserAuth(IUserAuth existingUser, IUserAuth newUser) =>
        Exec(db => UpdateUserAuth(db, existingUser, newUser));

    public virtual IUserAuth UpdateUserAuth(IDbConnection db, IUserAuth existingUser, IUserAuth newUser)
    {
        newUser.ValidateNewUser();
        AssertNoExistingUser(db, newUser, existingUser);

        newUser.Id = existingUser.Id;
        newUser.PasswordHash = existingUser.PasswordHash;
        newUser.Salt = existingUser.Salt;
        newUser.DigestHa1Hash = existingUser.DigestHa1Hash;
        newUser.CreatedDate = existingUser.CreatedDate;
        newUser.ModifiedDate = DateTime.UtcNow;

        db.Save((TUserAuth)newUser);

        return newUser;
    }

    public virtual IUserAuth? GetUserAuthByUserName(string? userNameOrEmail)
    {
        if (userNameOrEmail == null)
            return null;

        return Exec(db =>
        {
            if (!hasInitSchema)
            {
                hasInitSchema = db.TableExists<TUserAuth>();

                if (!hasInitSchema)
                    throw new Exception("OrmLiteAuthRepository Db tables have not been initialized. Try calling 'InitSchema()' in your AppHost Configure method.");
            }
            return GetUserAuthByUserName(db, userNameOrEmail);
        });
    }

    private TUserAuth? GetUserAuthByUserName(IDbConnection db, string userNameOrEmail)
    {
        var isEmail = userNameOrEmail.Contains("@");
        var lowerUserName = userNameOrEmail.ToLower();
            
        TUserAuth? userAuth = null;

        // Usernames/Emails are saved in Lower Case so we can do an exact search using lowerUserName
        if (HostContext.GetPlugin<AuthFeature>()?.SaveUserNamesInLowerCase == true)
        {
            return isEmail
                ? db.Select<TUserAuth>(q => q.Email == lowerUserName).FirstOrDefault()
                : db.Select<TUserAuth>(q => q.UserName == lowerUserName).FirstOrDefault();
        }
            
        // Try an exact search using index first
        userAuth = isEmail
            ? db.Select<TUserAuth>(q => q.Email == userNameOrEmail).FirstOrDefault()
            : db.Select<TUserAuth>(q => q.UserName == userNameOrEmail).FirstOrDefault();

        if (userAuth != null)
            return userAuth;

        // Fallback to a non-index search if no exact match is found
        if (ForceCaseInsensitiveUserNameSearch)
        {
            userAuth = isEmail
                ? db.Select<TUserAuth>(q => q.Email.ToLower() == lowerUserName).FirstOrDefault()
                : db.Select<TUserAuth>(q => q.UserName.ToLower() == lowerUserName).FirstOrDefault();
        }
            
        return userAuth;
    }

    private void SetOrderBy(SqlExpression<TUserAuth> q, string? orderBy)
    {
        if (string.IsNullOrEmpty(orderBy))
            return;
            
        var orderByField = AuthRepositoryUtils.ParseOrderBy(orderBy, out var desc);
        if (!desc)
            q.OrderBy(orderByField);
        else
            q.OrderByDescending(orderByField);
    }

    public List<IUserAuth> GetUserAuths(string? orderBy = null, int? skip = null, int? take = null) =>
        Exec(db => GetUserAuths(db, orderBy, skip, take));

    public List<IUserAuth> GetUserAuths(IDbConnection db, string? orderBy = null, int? skip = null, int? take = null)
    {
        var q = db.From<TUserAuth>();
        SetOrderBy(q, orderBy);
        if (skip != null || take != null)
            q.Limit(skip, take);
        return db.Select(q).ConvertAll(x => (IUserAuth)x);
    }

    public List<IUserAuth> SearchUserAuths(string query, string? orderBy = null, int? skip = null, int? take = null) =>
        Exec(db => SearchUserAuths(db, query, orderBy, skip, take));

    public List<IUserAuth> SearchUserAuths(IDbConnection db, string query, string? orderBy = null, int? skip = null, int? take = null)
    {
        var q = db.From<TUserAuth>();
        if (!string.IsNullOrEmpty(query))
        {
            q.Where(x => x.UserName.Contains(query) ||
                         x.PrimaryEmail.Contains(query) ||
                         x.Email.Contains(query) ||
                         x.DisplayName.Contains(query) ||
                         x.Company.Contains(query));
        }
        SetOrderBy(q, orderBy);
        if (skip != null || take != null)
            q.Limit(skip, take);
        return db.Select(q).ConvertAll(x => (IUserAuth)x);
    }

    public virtual bool TryAuthenticate(string userName, string password, out IUserAuth? userAuth)
    {
        userAuth = GetUserAuthByUserName(userName);
        if (userAuth == null)
            return false;

        if (userAuth.VerifyPassword(password, out var needsRehash))
        {
            this.RecordSuccessfulLogin(userAuth, needsRehash, password);
            return true;
        }

        this.RecordInvalidLoginAttempt(userAuth);

        userAuth = null;
        return false;
    }

    public virtual bool TryAuthenticate(Dictionary<string, string> digestHeaders, string privateKey, int nonceTimeOut, string sequence, out IUserAuth? userAuth)
    {
        userAuth = GetUserAuthByUserName(digestHeaders["username"]);
        if (userAuth == null)
            return false;

        if (userAuth.VerifyDigestAuth(digestHeaders, privateKey, nonceTimeOut, sequence))
        {
            this.RecordSuccessfulLogin(userAuth);
            return true;
        }

        this.RecordInvalidLoginAttempt(userAuth);

        userAuth = null;
        return false;
    }

    public virtual void DeleteUserAuth(string userAuthId) => Exec(db => DeleteUserAuth(db, userAuthId));
    public virtual void DeleteUserAuth(IDbConnection db, string userAuthId)
    {
        using var trans = db.OpenTransaction();
        var userId = int.Parse(userAuthId);

        db.Delete<TUserAuth>(x => x.Id == userId);
        db.Delete<TUserAuthDetails>(x => x.UserAuthId == userId);
        if (UseDistinctRoleTables)
            db.Delete<UserAuthRole>(x => x.UserAuthId == userId);

        trans.Commit();
    }

    public virtual void LoadUserAuth(IAuthSession session, IAuthTokens tokens)
    {
        if (session == null)
            throw new ArgumentNullException(nameof(session));

        var userAuth = GetUserAuth(session, tokens);
        LoadUserAuth(session, userAuth);
    }

    private void LoadUserAuth(IAuthSession session, IUserAuth userAuth)
    {
        session.PopulateSession(userAuth, this);
    }

    public virtual IUserAuth? GetUserAuth(string userAuthId) => Exec(db => GetUserAuth(db, userAuthId));

    public virtual IUserAuth? GetUserAuth(IDbConnection db, string userAuthId)
    {
        if (string.IsNullOrEmpty(userAuthId))
            throw new ArgumentNullException(nameof(userAuthId));

        return db.SingleById<TUserAuth>(int.Parse(userAuthId));
    }

    public virtual void SaveUserAuth(IAuthSession authSession) => Exec(db => SaveUserAuth(db, authSession));

    public virtual void SaveUserAuth(IDbConnection db, IAuthSession authSession)
    {
        if (authSession == null)
            throw new ArgumentNullException(nameof(authSession));

        var userAuth = !authSession.UserAuthId.IsNullOrEmpty()
            ? db.SingleById<TUserAuth>(int.Parse(authSession.UserAuthId))
            : authSession.ConvertTo<TUserAuth>();

        if (userAuth.Id == 0 && !authSession.UserAuthId.IsNullOrEmpty())
            userAuth.Id = int.Parse(authSession.UserAuthId);

        userAuth.ModifiedDate = DateTime.UtcNow;
        if (userAuth.CreatedDate == default(DateTime))
            userAuth.CreatedDate = userAuth.ModifiedDate;

        db.Save(userAuth);
    }

    public virtual void SaveUserAuth(IUserAuth userAuth) => Exec(db => SaveUserAuth(db, userAuth));

    public virtual void SaveUserAuth(IDbConnection db, IUserAuth userAuth)
    {
        if (userAuth == null)
            throw new ArgumentNullException(nameof(userAuth));

        userAuth.ModifiedDate = DateTime.UtcNow;
        if (userAuth.CreatedDate == default(DateTime))
            userAuth.CreatedDate = userAuth.ModifiedDate;

        db.Save((TUserAuth)userAuth);
    }

    public virtual List<IUserAuthDetails> GetUserAuthDetails(string userAuthId) => Exec(db => GetUserAuthDetails(db, userAuthId));

    public virtual List<IUserAuthDetails> GetUserAuthDetails(IDbConnection db, string userAuthId)
    {
        var id = int.Parse(userAuthId);
        return db.Select<TUserAuthDetails>(q => q.UserAuthId == id).OrderBy(x => x.ModifiedDate).Cast<IUserAuthDetails>().ToList();
    }

    public virtual IUserAuth? GetUserAuth(IAuthSession authSession, IAuthTokens? tokens)
    {
        if (!authSession.UserAuthId.IsNullOrEmpty())
        {
            var userAuth = GetUserAuth(authSession.UserAuthId);
            if (userAuth != null)
                return userAuth;
        }
        if (!authSession.UserAuthName.IsNullOrEmpty())
        {
            var userAuth = GetUserAuthByUserName(authSession.UserAuthName);
            if (userAuth != null)
                return userAuth;
        }

        if (tokens == null || tokens.Provider.IsNullOrEmpty() || tokens.UserId.IsNullOrEmpty())
            return null;

        return Exec(db =>
        {
            var oAuthProvider = db.Select<TUserAuthDetails>(q =>
                q.Provider == tokens.Provider && q.UserId == tokens.UserId).FirstOrDefault();

            if (oAuthProvider != null)
            {
                var userAuth = db.SingleById<TUserAuth>(oAuthProvider.UserAuthId);
                return userAuth;
            }
            return null;
        });
    }

    public virtual IUserAuthDetails CreateOrMergeAuthSession(IAuthSession authSession, IAuthTokens tokens)
    {
        TUserAuth userAuth = GetUserAuth(authSession, tokens) as TUserAuth 
                             ?? typeof(TUserAuth).CreateInstance<TUserAuth>();

        return Exec(db =>
        {
            var authDetails = db.Select<TUserAuthDetails>(
                q => q.Provider == tokens.Provider && q.UserId == tokens.UserId).FirstOrDefault();

            if (authDetails == null)
            {
                authDetails = typeof(TUserAuthDetails).CreateInstance<TUserAuthDetails>();
                authDetails.Provider = tokens.Provider;
                authDetails.UserId = tokens.UserId;
            }

            authDetails.PopulateMissing(tokens, overwriteReserved: true);
            userAuth.PopulateMissingExtended(authDetails);

            userAuth.ModifiedDate = DateTime.UtcNow;
            if (userAuth.CreatedDate == default(DateTime))
                userAuth.CreatedDate = userAuth.ModifiedDate;

            db.Save(userAuth);

            authDetails.UserAuthId = userAuth.Id;

            authDetails.ModifiedDate = userAuth.ModifiedDate;
            if (authDetails.CreatedDate == default(DateTime))
                authDetails.CreatedDate = userAuth.ModifiedDate;

            db.Save(authDetails);

            return authDetails;
        });
    }

    public virtual void Clear() => Exec(Clear);
    public virtual void Clear(IDbConnection db)
    {
        db.DeleteAll<TUserAuth>();
        db.DeleteAll<TUserAuthDetails>();
        if (UseDistinctRoleTables)
            db.DeleteAll<UserAuthRole>();
    }

    string AssertUserAuthId(string userAuthId)
    {
        if (userAuthId == null)
            throw new ArgumentNullException(nameof(userAuthId));
        return userAuthId;
    }

    public virtual ICollection<string> GetRoles(string userAuthId)
    {
        AssertUserAuthId(userAuthId);
        if (!UseDistinctRoleTables)
        {
            var userAuth = GetUserAuth(userAuthId);
            return userAuth?.Roles ?? TypeConstants.EmptyStringList; ;
        }
        else
        {
            return Exec(db => GetRoles(db, userAuthId));
        }
    }

    public virtual ICollection<string> GetRoles(IDbConnection db, string userAuthId)
    {
        if (!UseDistinctRoleTables)
        {
            var userAuth = GetUserAuth(db, userAuthId);
            return userAuth?.Roles ?? TypeConstants.EmptyStringList; ;
        }
        else
        {
            return db.Select<UserAuthRole>(q => q.UserAuthId == int.Parse(userAuthId) && q.Role != null).ConvertAll(x => x.Role);
        }
    }

    public virtual ICollection<string> GetPermissions(string userAuthId)
    {
        AssertUserAuthId(userAuthId);
        if (!UseDistinctRoleTables)
        {
            var userAuth = GetUserAuth(userAuthId);
            return userAuth?.Permissions ?? TypeConstants.EmptyStringList; ;
        }
        else
        {
            return Exec(db => GetPermissions(db, userAuthId));
        }
    }

    public virtual ICollection<string> GetPermissions(IDbConnection db, string userAuthId)
    {
        AssertUserAuthId(userAuthId);
        if (!UseDistinctRoleTables)
        {
            var userAuth = GetUserAuth(db, userAuthId);
            return userAuth?.Permissions ?? TypeConstants.EmptyStringList; ;
        }
        else
        {
            return db.Select<UserAuthRole>(q => q.UserAuthId == int.Parse(userAuthId) && q.Permission != null).ConvertAll(x => x.Permission);
        }
    }

    public virtual void GetRolesAndPermissions(string userAuthId, out ICollection<string> roles, out ICollection<string> permissions)
    {
        AssertUserAuthId(userAuthId);
        if (!UseDistinctRoleTables)
        {
            var userAuth = GetUserAuth(userAuthId);
            if (userAuth == null)
            {
                roles = permissions = TypeConstants.EmptyStringArray;
                return;
            }

            roles = userAuth.Roles;
            permissions = userAuth.Permissions;
        }
        else
        {
            roles = new List<string>();
            permissions = new List<string>();
                
            var rolesAndPerms = Exec(db =>
            {
                return db.KeyValuePairs<string,string>(db.From<UserAuthRole>()
                    .Where(x => x.UserAuthId == int.Parse(userAuthId))
                    .Select(x => new { x.Role, x.Permission })); 
            });

            foreach (var kvp in rolesAndPerms)
            {
                if (kvp.Key != null)
                    roles.Add(kvp.Key);
                if (kvp.Value != null)
                    permissions.Add(kvp.Value);
            }                
        }
    }

    public virtual bool HasRole(string? userAuthId, string role)
    {
        if (role == null)
            throw new ArgumentNullException(nameof(role));

        if (userAuthId == null)
            return false;

        if (!UseDistinctRoleTables)
        {
            var userAuth = GetUserAuth(userAuthId);
            return userAuth?.Roles != null && userAuth.Roles.Contains(role);
        }
        else
        {
            return Exec(db => {
                return db.Count<UserAuthRole>(q =>
                    q.UserAuthId == int.Parse(userAuthId) && q.Role == role) > 0;
            });
        }
    }

    public virtual bool HasPermission(string? userAuthId, string permission)
    {
        if (permission == null)
            throw new ArgumentNullException(nameof(permission));

        if (userAuthId == null)
            return false;

        if (!UseDistinctRoleTables)
        {
            var userAuth = GetUserAuth(userAuthId);
            return userAuth?.Permissions != null && userAuth.Permissions.Contains(permission);
        }
        else
        {
            return Exec(db => {
                return db.Count<UserAuthRole>(q =>
                    q.UserAuthId == int.Parse(userAuthId) && q.Permission == permission) > 0;
            });
        }
    }

    public virtual void AssignRoles(string userAuthId, ICollection<string>? roles = null, ICollection<string>? permissions = null)
    {
        var userAuth = GetUserAuth(userAuthId);
        if (userAuth == null)
            return;
        if (!UseDistinctRoleTables)
        {
            if (!roles.IsEmpty())
            {
                foreach (var missingRole in roles.Where(x => userAuth.Roles == null || !userAuth.Roles.Contains(x)))
                {
                    userAuth.Roles ??= new List<string>();
                    userAuth.Roles.Add(missingRole);
                }
            }

            if (!permissions.IsEmpty())
            {
                foreach (var missingPermission in permissions.Where(x => userAuth.Permissions == null || !userAuth.Permissions.Contains(x)))
                {
                    userAuth.Permissions ??= new List<string>();
                    userAuth.Permissions.Add(missingPermission);
                }
            }

            SaveUserAuth(userAuth);
        }
        else
        {
            Exec(db => AssignRoles(db, userAuthId, roles, permissions));
        }
    }

    public virtual void AssignRoles(IDbConnection db, string userAuthId, ICollection<string>? roles = null, ICollection<string>? permissions = null)
    {
        var userAuth = GetUserAuth(db, userAuthId);
        if (userAuth == null)
            return;
        if (!UseDistinctRoleTables)
        {
            if (!roles.IsEmpty())
            {
                foreach (var missingRole in roles.Where(x => userAuth.Roles == null || !userAuth.Roles.Contains(x)))
                {
                    userAuth.Roles ??= new List<string>();
                    userAuth.Roles.Add(missingRole);
                }
            }

            if (!permissions.IsEmpty())
            {
                foreach (var missingPermission in permissions.Where(x => userAuth.Permissions == null || !userAuth.Permissions.Contains(x)))
                {
                    userAuth.Permissions ??= new List<string>();
                    userAuth.Permissions.Add(missingPermission);
                }
            }

            SaveUserAuth(db, userAuth);
        }
        else
        {
            var now = DateTime.UtcNow;
            var userRoles = db.Select<UserAuthRole>(q => q.UserAuthId == userAuth.Id);

            if (!roles.IsEmpty())
            {
                var roleSet = userRoles.Where(x => x.Role != null).Select(x => x.Role).ToSet();
                foreach (var role in roles)
                {
                    if (!roleSet.Contains(role))
                    {
                        db.Insert(new UserAuthRole
                        {
                            UserAuthId = userAuth.Id,
                            Role = role,
                            CreatedDate = now,
                            ModifiedDate = now,
                        });
                    }
                }
            }

            if (!permissions.IsEmpty())
            {
                var permissionSet = userRoles.Where(x => x.Permission != null).Select(x => x.Permission).ToSet();
                foreach (var permission in permissions)
                {
                    if (!permissionSet.Contains(permission))
                    {
                        db.Insert(new UserAuthRole
                        {
                            UserAuthId = userAuth.Id,
                            Permission = permission,
                            CreatedDate = now,
                            ModifiedDate = now,
                        });
                    }
                }
            }
        }
    }

    public virtual void UnAssignRoles(string userAuthId, ICollection<string>? roles = null, ICollection<string>? permissions = null)
    {
        var userAuth = GetUserAuth(userAuthId);
        if (userAuth == null)
            return;
        if (!UseDistinctRoleTables)
        {
            roles.Each(x => userAuth.Roles.Remove(x));
            permissions.Each(x => userAuth.Permissions.Remove(x));

            if (roles != null || permissions != null)
            {
                SaveUserAuth(userAuth);
            }
        }
        else
        {
            Exec(db => UnAssignRoles(db, userAuthId, roles, permissions));
        }
    }

    public virtual void UnAssignRoles(IDbConnection db, string userAuthId, ICollection<string>? roles = null, ICollection<string>? permissions = null)
    {
        var userAuth = GetUserAuth(db, userAuthId);
        if (userAuth == null)
            return;
        if (!UseDistinctRoleTables)
        {
            roles.Each(x => userAuth.Roles.Remove(x));
            permissions.Each(x => userAuth.Permissions.Remove(x));

            if (roles != null || permissions != null)
            {
                SaveUserAuth(db, userAuth);
            }
        }
        else
        {
            if (!roles.IsEmpty())
            {
                db.Delete<UserAuthRole>(q => q.UserAuthId == userAuth.Id && roles.Contains(q.Role));
            }
            if (!permissions.IsEmpty())
            {
                db.Delete<UserAuthRole>(q => q.UserAuthId == userAuth.Id && permissions.Contains(q.Permission));
            }
        }
    }

    public virtual void InitApiKeySchema() => Exec(InitApiKeySchema);
    public virtual void InitApiKeySchema(IDbConnection db) => db.CreateTableIfNotExists<ApiKey>();
    public virtual void DropApiKeySchema() => Exec(DropApiKeySchema);
    public virtual void DropApiKeySchema(IDbConnection db) => db.DropTable<ApiKey>();

    public bool ApiKeyExists(string apiKey)
    {
        return Exec(db =>
        {
            return db.Exists<ApiKey>(x => x.Id == apiKey);
        });
    }

    public ApiKey GetApiKey(string apiKey)
    {
        return Exec(db => db.SingleById<ApiKey>(apiKey));
    }

    public List<ApiKey> GetUserApiKeys(string userId)
    {
        return Exec(db =>
        {
            var q = db.From<ApiKey>()
                .Where(x => x.UserAuthId == userId)
                .And(x => x.CancelledDate == null)
                .And(x => x.ExpiryDate == null || x.ExpiryDate >= DateTime.UtcNow)
                .OrderByDescending(x => x.CreatedDate);

            return db.Select(q);
        });
    }

    public void StoreAll(IEnumerable<ApiKey> apiKeys)
    {
        Exec(db =>
        {
            db.SaveAll(apiKeys);
        });
    }

    public IUserAuth CreateUserAuth() => (IUserAuth) typeof(TUserAuth).CreateInstance();

    public IUserAuthDetails CreateUserAuthDetails() => (IUserAuthDetails)typeof(TUserAuthDetails).CreateInstance();
}